package com.upup.train_management.repository;

import com.upup.train_management.entity.Schedule;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.List;

@Repository
public interface ScheduleRepository extends JpaRepository<Schedule, Long> {
    List<Schedule> findByTrainId(Long trainId);
    List<Schedule> findByRouteId(Long routeId);
    List<Schedule> findByDepartureDate(LocalDate date);
    List<Schedule> findByDepartureDateBetween(LocalDate startDate, LocalDate endDate);

    @Query("SELECT s FROM Schedule s WHERE (s.departureDate > :currentDate) OR " +
            "(s.departureDate = :currentDate AND s.departureTime > :currentTime)")
    List<Schedule> findByDepartureDateTimeAfter(
            @Param("currentDate") LocalDate currentDate,
            @Param("currentTime") LocalTime currentTime);

    @Query("SELECT s FROM Schedule s JOIN RouteStation rs1 ON s.routeId = rs1.routeId " +
            "JOIN RouteStation rs2 ON s.routeId = rs2.routeId " +
            "WHERE rs1.stationId = :startStationId AND rs2.stationId = :endStationId " +
            "AND rs1.stationOrder < rs2.stationOrder " +
            "AND s.departureDate = :date")
    List<Schedule> findSchedulesByStationsAndDate(
            @Param("startStationId") Long startStationId,
            @Param("endStationId") Long endStationId,
            @Param("date") LocalDate date);
}